﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class SANPHAMDAO
    {
        public DataTable LoadSanPham()
        {
            DataTable dtSP = new DataTable();
            SqlConnection cn = Connection.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_BangSP", cn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dtSP);
            cn.Close();
            return dtSP;
        }

        public void ThemSanPham(SANPHAMDTO spDTO)
        {
            SqlConnection cn = Connection.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_ThemSP", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@tensp", SqlDbType.NVarChar);
            cmd.Parameters.Add("@maxx", SqlDbType.Int);
            cmd.Parameters.Add("@ghkho", SqlDbType.Int);
            cmd.Parameters.Add("@ghquay",SqlDbType.Int);
            cmd.Parameters.Add("@slhttk",SqlDbType.Int);
            cmd.Parameters.Add("@slhttq",SqlDbType.Int);
            cmd.Parameters.Add("@loaisp",SqlDbType.Int);
            cmd.Parameters.Add("@makho",SqlDbType.Int);
            cmd.Parameters.Add("@maquay",SqlDbType.Int);
            cmd.Parameters.Add("@gia",SqlDbType.Int);
            cmd.Parameters.Add("@tinhtrang",SqlDbType.Char);
            cmd.Parameters.Add("@ngaynhap",SqlDbType.DateTime);
            //gán giá trị cho tham số
            cmd.Parameters["@tensp"].Value = spDTO.TenSP;
            cmd.Parameters["@maxx"].Value = spDTO.MaXX;
            cmd.Parameters["@ghkho"].Value = spDTO.GHKho;
            cmd.Parameters["@ghquay"].Value = spDTO.GHQuay;
            cmd.Parameters["@slhttk"].Value = spDTO.SoLuongHTTrongKho;
            cmd.Parameters["@slhttq"].Value = spDTO.SoLuongHTTrongQuay;
            cmd.Parameters["@loaisp"].Value = spDTO.LoaiSP;
            cmd.Parameters["@makho"].Value = spDTO.MaKho;
            cmd.Parameters["@maquay"].Value = spDTO.MaQuay;
            cmd.Parameters["@gia"].Value = spDTO.Gia;
            cmd.Parameters["@tinhtrang"].Value = spDTO.TinhTrang;
            cmd.Parameters["@ngaynhap"].Value = spDTO.NgayNhap;
            //thực thi câu truy vấn
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public void XoaSanPham(int ma)
        {
            SqlConnection cn = Connection.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_XoaSP", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ma", SqlDbType.Int);
            cmd.Parameters["@ma"].Value = ma;
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public void SuaSanPham(SANPHAMDTO spDto)
        {
            SqlConnection cn = Connection.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_SuaSP", cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ma", SqlDbType.Int);
            cmd.Parameters.Add("@tensp", SqlDbType.NVarChar);
            cmd.Parameters.Add("@maxx", SqlDbType.Int);
            cmd.Parameters.Add("@ghkho", SqlDbType.Int);
            cmd.Parameters.Add("@ghquay", SqlDbType.Int);
            cmd.Parameters.Add("@slhttk", SqlDbType.Int);
            cmd.Parameters.Add("@slhttq", SqlDbType.Int);
            cmd.Parameters.Add("@loaisp", SqlDbType.Int);
            cmd.Parameters.Add("@makho", SqlDbType.Int);
            cmd.Parameters.Add("@maquay", SqlDbType.Int);
            cmd.Parameters.Add("@gia", SqlDbType.Int);
            cmd.Parameters.Add("@tinhtrang", SqlDbType.Char);
            cmd.Parameters.Add("@ngaynhap", SqlDbType.DateTime);
            //gán giá trị cho tham số
            cmd.Parameters["@ma"].Value = spDto.MaSP;
            cmd.Parameters["@tensp"].Value = spDto.TenSP;
            cmd.Parameters["@maxx"].Value = spDto.MaXX;
            cmd.Parameters["@ghkho"].Value = spDto.GHKho;
            cmd.Parameters["@ghquay"].Value = spDto.GHQuay;
            cmd.Parameters["@slhttk"].Value = spDto.SoLuongHTTrongKho;
            cmd.Parameters["@slhttq"].Value = spDto.SoLuongHTTrongQuay;
            cmd.Parameters["@loaisp"].Value = spDto.LoaiSP;
            cmd.Parameters["@makho"].Value = spDto.MaKho;
            cmd.Parameters["@maquay"].Value = spDto.MaQuay;
            cmd.Parameters["@gia"].Value = spDto.Gia;
            cmd.Parameters["@tinhtrang"].Value = spDto.TinhTrang;
            cmd.Parameters["@ngaynhap"].Value = spDto.NgayNhap;
            //thực thi câu truy vấn
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public DataTable LoadTimKiem(string tukhoa)
        {
            DataTable dt = new DataTable();
            SqlConnection cn = Connection.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_TimSP", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@tukhoa", SqlDbType.VarChar);

            cmd.Parameters["@tukhoa"].Value = tukhoa;

            cmd.ExecuteNonQuery();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        public DataTable LoadTim(string tukhoa)
        {
            DataTable dt = new DataTable();
            SqlConnection cn = Connection.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_TimSL", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@tukhoa", SqlDbType.VarChar);

            cmd.Parameters["@tukhoa"].Value = tukhoa;

            cmd.ExecuteNonQuery();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        public void SuaSoLuong(int ma,int ghkho,int ghquay,int slhtkho,int slhtquay)
        {
            SqlConnection cn = Connection.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_SuaSL", cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ma", SqlDbType.Int);
            cmd.Parameters.Add("@ghkho", SqlDbType.Int);
            cmd.Parameters.Add("@ghquay", SqlDbType.Int);
            cmd.Parameters.Add("@slhttk", SqlDbType.Int);
            cmd.Parameters.Add("@slhttq", SqlDbType.Int);
            //gán giá trị cho tham số
            cmd.Parameters["@ma"].Value = ma;
            cmd.Parameters["@ghkho"].Value = ghkho;
            cmd.Parameters["@ghquay"].Value = ghquay;
            cmd.Parameters["@slhttk"].Value = slhtkho;
            cmd.Parameters["@slhttq"].Value = slhtquay;
            //thực thi câu truy vấn
            cmd.ExecuteNonQuery();
            cn.Close();
        }
    }
}
